package com.swordget.dao.impl;

import cn.hutool.core.net.NetUtil;
import cn.hutool.core.util.IdUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.swordget.commons.JdbcUtil;
import com.swordget.dao.Sys_DataSourceDao;
import com.swordget.entity.Sys_DataSource;
import com.swordget.entity.vo.SimplePageRequestVo;
import com.swordget.entity.vo.SimplePageResponseVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class Sys_DataSourceDaoImpl implements Sys_DataSourceDao {

    private final static Logger LOG = LoggerFactory.getLogger(Sys_DataSourceDaoImpl.class);

    @Autowired
    private DruidDataSource dataSource;

    @Autowired
    private JdbcUtil jdbcUtil;

    @Override
    public SimplePageResponseVo findAll(SimplePageRequestVo pageVo) {
        int rows = pageVo.getRows();
        int page = pageVo.getPage();
        String querySql = "SELECT * FROM (" +
                "SELECT ID, IP_ADDRESS, PORT_NUMBER, DATABASE_NAME, DATABASE_TYPE, USER_NAME, PASSWORD, DATA_SOURCE_NAME, ROWNUM AS rowno FROM (" +
                "SELECT ID, IP_ADDRESS, PORT_NUMBER, DATABASE_NAME, DATABASE_TYPE, USER_NAME, PASSWORD, DATA_SOURCE_NAME FROM SYS_DATA_SOURCE) t WHERE ROWNUM <= ?" +
                ") WHERE rowno >= ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        List<Sys_DataSource> sysDataSourceList = new ArrayList<>();
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(querySql);
            ps.setInt(1, (page * rows));
            ps.setInt(2, (page - 1) * rows + 1);
            rs = ps.executeQuery();

            while (rs.next()) {
                String id = rs.getString("ID");
                String ipAddress = rs.getString("IP_ADDRESS");
                String portNumber = rs.getString("PORT_NUMBER");
                String databaseName = rs.getString("DATABASE_NAME");
                String databaseType = rs.getString("DATABASE_TYPE");
                String userName = rs.getString("USER_NAME");
                String password = rs.getString("PASSWORD");
                String dataSourceName = rs.getString("DATA_SOURCE_NAME");

                Sys_DataSource sysDataSource = Sys_DataSource.builder()
                        .id(id)
                        .ipAddress(ipAddress)
                        .portNumber(portNumber)
                        .databaseName(databaseName)
                        .databaseType(databaseType)
                        .userName(userName)
                        .password(password)
                        .dataSourceName(dataSourceName)
                        .build();

                sysDataSourceList.add(sysDataSource);
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("程序执行过程中出现异常", e);
            }
        }


        String countSql = String.format("SELECT COUNT(*) FROM SYS_DATA_SOURCE");
        return SimplePageResponseVo.builder()
                .rows(sysDataSourceList)
                .total(jdbcUtil.executeCount(countSql))
                .build();
    }

    @Override
    public List<Sys_DataSource> findAll() {
        String sql = "SELECT ID, IP_ADDRESS, PORT_NUMBER, DATABASE_NAME, DATABASE_TYPE, USER_NAME, PASSWORD, DATA_SOURCE_NAME FROM SYS_DATA_SOURCE";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        List<Sys_DataSource> sysDataSourceList = new ArrayList<>();
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                String id = rs.getString("ID");
                String ipAddress = rs.getString("IP_ADDRESS");
                String portNumber = rs.getString("PORT_NUMBER");
                String databaseName = rs.getString("DATABASE_NAME");
                String databaseType = rs.getString("DATABASE_TYPE");
                String userName = rs.getString("USER_NAME");
                String password = rs.getString("PASSWORD");
                String dataSourceName = rs.getString("DATA_SOURCE_NAME");

                Sys_DataSource sysDataSource = Sys_DataSource.builder()
                        .id(id)
                        .ipAddress(ipAddress)
                        .portNumber(portNumber)
                        .databaseName(databaseName)
                        .databaseType(databaseType)
                        .userName(userName)
                        .password(password)
                        .dataSourceName(dataSourceName)
                        .build();

                sysDataSourceList.add(sysDataSource);
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("程序执行过程中出现异常", e);
            }
        }
        return sysDataSourceList;
    }

    @Override
    public Sys_DataSource flindOne(String id) {
        String sql = "SELECT ID, IP_ADDRESS, PORT_NUMBER, DATABASE_NAME, DATABASE_TYPE, USER_NAME, PASSWORD," +
                " DATA_SOURCE_NAME FROM SYS_DATA_SOURCE WHERE ID = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        Sys_DataSource sysDataSource = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                sysDataSource = Sys_DataSource.builder()
                        .id(rs.getString("ID"))
                        .ipAddress(rs.getString("IP_ADDRESS"))
                        .portNumber(rs.getString("PORT_NUMBER"))
                        .databaseName(rs.getString("DATABASE_NAME"))
                        .databaseType(rs.getString("DATABASE_TYPE"))
                        .userName(rs.getString("USER_NAME"))
                        .password(rs.getString("PASSWORD"))
                        .dataSourceName(rs.getString("DATA_SOURCE_NAME"))
                        .build();
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("程序执行过程中出现异常", e);
            }
        }
        return sysDataSource;
    }

    @Override
    public boolean addSys_dataSource(Sys_DataSource sys_DataSource) {

        //终端ID
        Long workerId = NetUtil.ipv4ToLong(NetUtil.getLocalhostStr()) >> 16 & 31;
        //数据中心ID
        Long dataCenterId = 1L;
        //生成ID
        String id = IdUtil.getSnowflake(workerId, dataCenterId).nextIdStr();

        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "INSERT INTO SYS_DATA_SOURCE (ID, IP_ADDRESS, PORT_NUMBER, DATABASE_NAME, DATABASE_TYPE," +
                " USER_NAME, PASSWORD, DATA_SOURCE_NAME) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, id);
            ps.setString(2, sys_DataSource.getIpAddress());
            ps.setString(3, sys_DataSource.getPortNumber());
            ps.setString(4, sys_DataSource.getDatabaseName());
            ps.setString(5, sys_DataSource.getDatabaseType());
            ps.setString(6, sys_DataSource.getUserName());
            ps.setString(7, sys_DataSource.getPassword());
            ps.setString(8, sys_DataSource.getDataSourceName());
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("程序执行过程中出现异常", e);
            }
        }
        return false;
    }

    @Override
    public boolean editSys_dataSource(Sys_DataSource sys_DataSource) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "UPDATE SYS_DATA_SOURCE SET IP_ADDRESS = ?, PORT_NUMBER = ?, DATABASE_NAME = ?," +
                " DATABASE_TYPE = ?, USER_NAME = ?, PASSWORD = ?, DATA_SOURCE_NAME = ? WHERE ID = ?";
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, sys_DataSource.getIpAddress());
            ps.setString(2, sys_DataSource.getPortNumber());
            ps.setString(3, sys_DataSource.getDatabaseName());
            ps.setString(4, sys_DataSource.getDatabaseType());
            ps.setString(5, sys_DataSource.getUserName());
            ps.setString(6, sys_DataSource.getPassword());
            ps.setString(7, sys_DataSource.getDataSourceName());
            ps.setString(8, sys_DataSource.getId());
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("程序执行过程中出现异常", e);
            }
        }
        return false;
    }
}
